"""
Case Type   : Compatibility
Case Name   : 验证执行语句中调用自定义函数、存储过程;部分测试点合理报错
Create At   : 2024/10/16
Owner       : liu-tong-8848
Description :
              1.在语句中创建自定义函数;
              2.创建自定义函数;
              3.在语句中调用自定义函数;
              4.在语句中创建存储过程;
              5.创建存储过程;
              6.在语句中调用存储过程;
              7.清理环境
Expect      :
              1.合理报错
              2.成功
              3.成功
              4.合理报错
              5.成功
              6.成功
              7.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.db_user_node = Node(node='PrimaryDbBmode')

    def test_add_dimension(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        step1 = '--step1:在语句中创建自定义函数;expect:合理报错'
        self.log.info(step1)
        sql = "drop function if exists f_prepare_006(); prepare test_prepare_006a from 'create or replace function " \
              + "f_prepare_006(c1 bigint) returns bigint as \$\$ begin return c1; end; \$\$language plpgsql;'"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("ERROR:  utility statements", result, "执行失败" + step1)

        step2 = '--step2:创建自定义函数;expect:成功'
        self.log.info(step2)
        sql = "create or replace function f_prepare_006(c1 bigint) returns bigint " \
              + "as \$\$ begin return c1; end; \$\$language plpgsql;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step2)

        step3 = '--step3:在语句中调用自定义函数;expect:成功'
        self.log.info(step3)
        sql = "prepare test_prepare_006a from 'select f_prepare_006(1)'; execute test_prepare_006a;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step3)

        step4 = '--step4:在语句中创建存储过程;expect:合理报错'
        self.log.info(step4)
        sql = "drop procedure if exists p_prepare_006(); drop table if exists t_prepare_006; " \
              + "create table t_prepare_006(c1 int); prepare test_prepare_006b from 'CREATE OR REPLACE PROCEDURE " \
              + "p_prepare_006() AS BEGIN insert into t_prepare_006 values(1); END;'"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("ERROR:  utility statements", result, "执行失败" + step4)

        step5 = '--step5:创建存储过程;expect:成功'
        self.log.info(step5)
        sql = "CREATE OR REPLACE PROCEDURE p_prepare_006() AS BEGIN insert into t_prepare_006 values(1); END;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step5)

        step6 = '--step6:在语句中调用存储过程;expect:成功'
        self.log.info(step6)
        sql = "prepare test_prepare_006b from 'select p_prepare_006()'; execute test_prepare_006b; " \
              + "select * from t_prepare_006; "
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step6)

    def tearDown(self):
        step7 = 'step7:清理环境 expect:清理环境成功'
        self.log.info(step7)
        clean_sql = "deallocate prepare test_prepare_006a; deallocate prepare test_prepare_006b;"
        self.primary_sh.execut_db_sql(clean_sql)
        self.log.info("prepare语句无法跨会话生效，由于自动化用例会跨会话执行这些语句，所以自动化结束时无需清理")
        clean_sql = "drop function if exists f_prepare_006(); drop table if exists t_prepare_006; " \
                    + "drop procedure if exists p_prepare_006(); "
        clean_result = self.primary_sh.execut_db_sql(clean_sql)
        self.assertNotIn("ERROR", clean_result, "执行失败" + step7)
        self.log.info(clean_result)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
